Using Excel Power Query as a client

 

Setting up Excel and Power Query

Microsoft Excel's Power Query module can retrieve data from Integration Services using an 'OData feed'.

Note for Excel 2013 and earlier: Power Query must be installed as an add-on. It is available for download from Microsoft.

Creating an OData feed in Excel 2016

1. Launch Microsoft Excel and navigate to the Data tab in the ribbon. Click the New Query button on the ribbon and select From Other Sources > From OData Feed

   

2. The OData feed dialog will open:

   

   Enter the URL (for example 'https://ips.mycompany.com/tenantname/PlanningSpaceDataflow/data/v1') of your PlanningSpace data source and click OK.

The data feeds that you create are stored in Excel for future use. Use the Recent Sources button in the Data ribbon to access a list.

Creating an OData feed in Excel 2013

1. Launch Microsoft Excel and navigate to the Power Query tab in the ribbon. Click the From Other Sources button on the ribbon and select From OData Feed.

   

2. The OData feed dialog will open.



Enter the URL (e.g. 'https://ips.mycompany.com/tenantname/PlanningSpaceDataflow/data/v1') of your PlanningSpace data source and click OK.

3. In the next dialog box you need to enter credentials for a valid PlanningSpace tenant user, using a username/password or an API Key (see Authentication for OData). Select the Basic option, and type in the credentials.

For version 16.5 Update 7 and later: A Windows authentication mode is available for SAML2-type user accounts; see Windows Authentication for OData. Select the Windows option in this case.

4. Click Save to continue.

5. The following window will open.

   

   From here you can select which parameters you want to load. Checking Select multiple items enables you to select more than one parameter.

   Note: If you receive the error 'URL segment is too large', rather than selecting your parameters within the tool, manually input your query into the OData Feed dialog. This will take you directly to the Query Editor (step 6).

   If you want to load the data without making any changes, click Load and proceed to step 8.

   To add or remove columns from the load, click Edit.

6. The Query Editor will open.



From here you can make adjustments to the data displayed before it is loaded.

7. When you are ready to load your data, click on the Close & Load button on the ribbon.
8. Your data will be loaded. Upon successful completion, you will be able to use it in Excel.

Server restrictions for queries on 'AllScalarVariablesWide' and 'AllPeriodicVariablesWide'

OData queries to the 'all variables' properties can result in very large data downloads, which could overload the server and impact on all PlanningSpace users.

Important: OData feed queries for 'AllScalarVariablesWide' and 'AllPeriodicVariablesWide' which are unfiltered will only return partial result set data, for the 'Gross' partner and the first run of the result set. To retrieve all of the data an explicit filter must be used for specific partners or runs.